using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class LeftTypeService
    {
        public static bool AddLeftType(LeftType leftType)
        {
            string sql =
                "INSERT LeftType (TypeTitle, TypeLink, LeftID)" +
                "VALUES (@TypeTitle, @TypeLink, @LeftID)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@TypeTitle", leftType.TypeTitle),
				new SqlParameter("@TypeLink", leftType.TypeLink),
				new SqlParameter("@LeftID", leftType.LeftID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        public static bool DeleteLeftTypeByLeftTypeID(int leftTypeID)
        {
            string sql = "DELETE LeftType WHERE LeftTypeID = @LeftTypeID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@LeftTypeID", leftTypeID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyLeftType(LeftType leftType)
        {
            string sql =
                "UPDATE LeftType " +
                "SET " +
                    "TypeTitle = @TypeTitle, " +
                    "TypeLink = @TypeLink, " +
                    "LeftID = @LeftID " +
                "WHERE LeftTypeID = @LeftTypeID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@LeftTypeID", leftType.LeftTypeID),
				new SqlParameter("@TypeTitle", leftType.TypeTitle),
				new SqlParameter("@TypeLink", leftType.TypeLink),
				new SqlParameter("@LeftID", leftType.LeftID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<LeftType> GetAllLeftTypes()
        {
            string sqlAll = "SELECT * FROM LeftType";
            return GetLeftTypesBySql(sqlAll);
        }

        public static IList<LeftType> GetLeftTypesParent()
        {
            string sqlAll = "SELECT * FROM LeftType where LeftID = 0";
            return GetLeftTypesBySql(sqlAll);
        }

        public static IList<LeftType> GetLeftTypesByLeftID(int Id)
        {
            string sqlAll = "SELECT * FROM LeftType where LeftID=@LeftID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@LeftID", Id)
			};
            return GetLeftTypesBySql(sqlAll, para);
        }

        public static LeftType GetLeftTypeByLeftTypeID(int leftTypeID)
        {
            string sql = "SELECT * FROM LeftType WHERE LeftTypeID = @LeftTypeID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@LeftTypeID", leftTypeID)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            LeftType leftType = new LeftType();

            foreach (DataRow dr in temp.Rows)
            {
                leftType.LeftTypeID = (int)dr["LeftTypeID"];
                leftType.TypeTitle = (string)dr["TypeTitle"];
                leftType.TypeLink = (string)dr["TypeLink"];
                leftType.LeftID = (int)dr["LeftID"];

            }
            return leftType;

        }

        private static IList<LeftType> GetLeftTypesBySql(string safeSql)
        {
            List<LeftType> list = new List<LeftType>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    LeftType leftType = new LeftType();

                    leftType.LeftTypeID = (int)dr["LeftTypeID"];
                    leftType.TypeTitle = (string)dr["TypeTitle"];
                    leftType.TypeLink = (string)dr["TypeLink"];
                    leftType.LeftID = (int)dr["LeftID"];

                    list.Add(leftType);
                }
            }
            return list;
        }

        private static IList<LeftType> GetLeftTypesBySql(string safeSql, params SqlParameter[] para)
        {
            List<LeftType> list = new List<LeftType>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    LeftType leftType = new LeftType();

                    leftType.LeftTypeID = (int)dr["LeftTypeID"];
                    leftType.TypeTitle = (string)dr["TypeTitle"];
                    leftType.TypeLink = (string)dr["TypeLink"];
                    leftType.LeftID = (int)dr["LeftID"];

                    list.Add(leftType);
                }
            }
            return list;
        }
    }
}
